package mango.orm.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import mango.Mango;
import mango.functions.FuncString;
import mango.orm.OrmException;
import mango.orm.OrmFactory;
import mango.orm.SQLParameter;
import mango.orm.DBSession;

public class Batcher {
    private DBSession session;
    private String sql;
    private String executeSql;
    private PreparedStatement statement;
    private boolean callable;
    private boolean batchable;
    private int paramCount = 0;
//    private Map<String, Integer> paramMap = new HashMap<String, Integer>();
    private Map<String, List<Integer>> paramMap;
    private boolean isInitialed = false;
    private boolean showSQL = false;
    private List<List<SQLParameter>> batchParameters;
    private String dbType;
    private List<String> subSql = new ArrayList<String>();
    
    private String sqlinfo;

    public Batcher(DBSession session, String sql) {
        this.batchParameters = new ArrayList<List<SQLParameter>>();
        this.session = session;
        this.sql = sql;
        this.initialParam();
        this.callable = this.isCallableSql(sql);
        this.executeSql = this.sql;
        this.batchable = true;
        if ("true".equalsIgnoreCase(Mango.getProperty(Mango.SHOWSQL))) {
            this.showSQL = true;
        }
        this.dbType = this.session.getConnector().getDbType();
    }
    
    public String getSql() {
        return this.sql;
    }

    public void setSql(String sql) {
        this.sql = sql;
        this.executeSql = sql;
        this.callable = this.isCallableSql(sql);
    }

    public String getExecuteSql() {
        return this.executeSql;
    }

    public void setExecuteSql(String executeSql) {
        this.executeSql = executeSql;
        this.callable = this.isCallableSql(sql);
    }

    public PreparedStatement getStatement() {
        return this.statement;
    }

    public void setStatement(PreparedStatement statement) {
        this.statement = statement;
    }

    public boolean isCallable() {
        return this.callable;
    }

    public boolean isBatchable() {
        return this.batchable;
    }

    public void setBatchable(boolean batchable) {
        this.batchable = batchable;
    }
    
    public void setSubSql(int i, String subsql) {
        if (i >= this.subSql.size()) {
            for (int j = this.subSql.size(); j <= i; j++) {
                this.subSql.add("");
            }
        }
        this.subSql.set(i, subsql);
    }
    
    public void setSubSql(String... subsql) {
        this.subSql.clear();
        for (int i = 0; i < subsql.length; i++) {
            this.subSql.add(subsql[i]);
        }
    }

    private void initialParam() {
        if (!this.isInitialed) {
            String regex = "@[a-zA-Z0-9]+";
            Pattern pattern = Pattern.compile(regex);
            Matcher matcher = pattern.matcher(this.sql);
            int cnt = 0;
            this.paramMap = new HashMap<String, List<Integer>>();
            while (matcher.find()) {
                cnt += 1;
                String param = matcher.group();
                String pname = param.substring(1).toUpperCase();
                List<Integer> list = this.paramMap.get(pname);
                if(list == null) {
                    list = new ArrayList<Integer>();
                }
                list.add(cnt);
                this.paramMap.put(pname, list);
            }
            this.sql = sql.replaceAll(regex, "?");
            this.paramCount = cnt;
            this.isInitialed = true;
        }
    }

    public void addBathParameters(List<SQLParameter> parameters) {
        this.batchParameters.add(parameters);
    }

    public void addParameter(SQLParameter parameter) {
        if (this.batchParameters.size() == 0) {
            this.batchParameters.add(new ArrayList<SQLParameter>());
        }
        this.batchParameters.get(this.batchParameters.size() - 1).add(parameter);
    }

    public void addBatch() {
        this.batchParameters.add(new ArrayList<SQLParameter>());
    }

    public PreparedStatement preparedStatement() throws SQLException {
        if (this.statement != null) {
            return this.statement;
        }
        if (this.subSql.size() > 0) {
//            this.executeSql = MessageFormat.format(this.executeSql, this.subSql.toArray());
            this.executeSql = OrmFactory.getSQLMaker(this.dbType).formatSubSql(this.executeSql, this.subSql);
        }
        if (this.callable) {
            this.statement = this.session
                    .getConnector()
                    .getConnection()
                    .prepareCall(this.executeSql, ResultSet.TYPE_FORWARD_ONLY,
                            ResultSet.CONCUR_READ_ONLY);
        }
        else {
            this.statement = this.session
                    .getConnector()
                    .getConnection()
                    .prepareStatement(this.executeSql, ResultSet.TYPE_FORWARD_ONLY,
                            ResultSet.CONCUR_READ_ONLY);
        }
        this.session.addStatement(this.statement);
        return this.statement;
    }

    public ResultSet executeQuery() throws SQLException {
        this.statement = this.preparedStatement();
        if (this.batchParameters.size() > 0) {
            List<SQLParameter> params = this.batchParameters.get(this.batchParameters.size() - 1);
            this.validateParam(params);
            this.setSqlInfo(this.getExecuteSql() + "|" + params);
            OrmFactory.getBinder(this.dbType).bind(this.statement,
                    params);
        }
        else {
            this.setSqlInfo(this.getExecuteSql());
        }
        ResultSet retSet = this.statement.executeQuery();
        this.session.addResultSet(retSet);
        return retSet;
    }

    public int executeUpdate() throws SQLException {
        this.statement = this.preparedStatement();
        if (this.batchParameters.size() > 0) {
            List<SQLParameter> params = this.batchParameters.get(this.batchParameters.size() - 1);
            this.validateParam(params);
            this.setSqlInfo(this.getSql() + "|" + params);
            OrmFactory.getBinder(this.dbType).bind(this.statement,
                    params);
        }
        else {
            this.setSqlInfo(this.getSql());
        }
        int ret = this.statement.executeUpdate();
        this.statement = null;
        this.setBatchable(false);
        return ret;
    }

    public boolean execute() throws SQLException {
        this.statement = this.preparedStatement();
        if (this.batchParameters.size() > 0) {
            List<SQLParameter> params = this.batchParameters.get(this.batchParameters.size() - 1);
            this.validateParam(params);
            this.setSqlInfo(this.getSql() + "|" + params);
            OrmFactory.getBinder(this.dbType).bind(this.statement,
                    params);
        }
        else {
            this.setSqlInfo(this.getSql());
        }
        boolean ret = this.statement.execute();
        this.statement = null;
        return ret;
    }

    public void executeBatch() throws SQLException {
        if (!this.session.getConnector().isTransactionSupported()) {
            this.executeUpdate();
            return;
        }
        this.statement = this.preparedStatement();
        this.setSqlInfo(this.getSql() + "|" + this.batchParameters);
        for (List<SQLParameter> params : this.batchParameters) {
            this.validateParam(params);
            OrmFactory.getBinder(this.dbType).bind(this.statement,
                    params);
            this.statement.addBatch();
        }
        this.statement.executeBatch();
        this.statement = null;
    }

    private void validateParam(List<SQLParameter> params) {
        int paramCnt2 = params.size();
        if (this.paramCount > paramCnt2) {
            for (int i = 0; i < paramCnt2; i++) {
                SQLParameter p = params.get(i);
                if(!FuncString.isBlank(p.getParamName())) {
                    for(int j = 0; j < this.paramMap.get(p.getParamName().toUpperCase()).size() - 1; j++) {
                        params.add(p.clone());
                    }
                }
            }
            if(this.paramCount > params.size()) {
                throw new OrmException("parameter is not enough!");
            }
        }
        Map<String, List<Integer>> pm = this.cloneParam();
        for (int i = 0; i < params.size(); i++) {
            SQLParameter param = params.get(i);
            if (!FuncString.isBlank(param.getParamName())) {
                List<Integer> indexs = pm.get(param.getParamName().toUpperCase());
                if (indexs != null) {
                    param.setIndex(indexs.remove(0));
                }
            }
            if (param.getIndex() < 1) {
                params.remove(i);
                i--;
            }
        }
    }
    
    private boolean isCallableSql(String sql) {
        String temp = sql.toLowerCase().replaceAll("\\p{Blank}*", "");
        if (temp.startsWith("{call")) {
            return true;
        }
        else {
            return false;
        }
    }
    
    private Map<String, List<Integer>> cloneParam(){
        Map<String, List<Integer>> pm = new HashMap<String, List<Integer>>();
        for(Iterator<String> i = this.paramMap.keySet().iterator(); i.hasNext();) {
            String k = i.next();
            List<Integer> ls = new ArrayList<Integer>(this.paramMap.get(k));
            pm.put(k, ls);
        }
        return pm;
    }
    
    public Batcher clone(DBSession session) {
        Batcher batcher = new Batcher(session, this.sql);
        batcher.sql = this.sql;
        batcher.callable  = this.callable;
        batcher.batchable = this.batchable;
        batcher.paramCount = this.paramCount;
        batcher.paramMap = this.paramMap;
        batcher.showSQL = this.showSQL;
        batcher.batchParameters = this.batchParameters;
        batcher.dbType =  this.dbType;
        batcher.subSql = this.subSql;
        return batcher;
    }
    
    private void setSqlInfo(String sql) {
        this.sqlinfo = sql;
        if(this.showSQL) {
            Mango.getLog().info(this.sqlinfo);
        }
    }
    
    public String getSqlInfo() {
        return this.sqlinfo;
    }
}
